package com.itheima.common.utils;
import com.itheima.common.annotion.ExcelAttribute;
import lombok.Getter;
import lombok.Setter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author qiankaofei
 * @version 1.0
 * @date 2020/11/20 8:48
 *
 * Excel 基于注解方式导入数据库
 */
@Setter
@Getter
public class ImportExcelUtils<T> {
    /**
     * 字节码对象
     */
    private Class clazz;

    /**
     * 属性数组
     */
    private Field fields[];

    public ImportExcelUtils(Class clazz){
        this.clazz=clazz;

        this.fields=clazz.getDeclaredFields();
    }

    /**
     *  解析excel
     */
    public List<T> readExcel(InputStream is, int rowIndex,int cellIndex) {
        List<T> resultList=new ArrayList<T>(0);
        try{
            Workbook workbook=new XSSFWorkbook(is);
            Sheet sheet = workbook.getSheetAt(0);
            T entity=null;
            for(Integer i=rowIndex;i<=sheet.getLastRowNum();i++){
                Row row=sheet.getRow(i);
                entity= (T) clazz.newInstance();
                for(Integer j=cellIndex;j<row.getLastCellNum();j++){
                    Cell cell=row.getCell(j);
                    for (Field field : fields) {
                        //判断该注解上有没有注解
                        if(field.isAnnotationPresent(ExcelAttribute.class)){
                            field.setAccessible(true);
                            //获取该注解
                            ExcelAttribute annotation = field.getAnnotation(ExcelAttribute.class);

                            if(j==annotation.sort()){
                                field.set(entity,covertAttrType(field,cell));
                            }
                        }
                    }
                }
                resultList.add(entity);
            }
        }catch (Exception e){
            e.getStackTrace();
        }
        return resultList;
    }

    /**
     *  类型转换
     */
    private Object covertAttrType(Field field, Cell cell) throws ParseException {
        String fieldType = field.getType().getSimpleName();
        if ("String".equals(fieldType)) {
            return getValue(cell);
        }else if ("Date".equals(fieldType)) {
            return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(getValue(cell)) ;
        }else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
            return Integer.parseInt(getValue(cell));
        }else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            return Double.parseDouble(getValue(cell));
        }else {
            return null;
        }
    }


    /**
     * 格式转为String
     * @param cell
     * @return
     */
    public String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date dt = DateUtil.getJavaDate(cell.getNumericCellValue());
                    return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(dt);
                } else {
                    // 防止数值变成科学计数法
                    String strCell = "";
                    Double num = cell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(num.toString());
                    if (bd != null) {
                        strCell = bd.toPlainString();
                    }
                    // 去除 浮点型 自动加的 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
}
